﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using XHD.BLL;
using XHD.Common;
using XHD.DBUtility;

namespace XHD.View.WebService
{
    public static class AppApiSQL
    {
        public static Tuple<DataSet, string> Login(string url, string ClientId, string uid, string pwd, string Token)
        {
                   BLL.hr_employee emp = new BLL.hr_employee();
            DataSet ds = new DataSet();
            if (Token != "")
                 ds = emp.GetList($" token = '{ Token }'   ");
            else
            {
                string password = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "MD5");
                  ds = emp.GetList($" uid = '{ uid }' and pwd = '{ password } ' ");
            }
            string msg = "";
            if (ds.Tables[0].Rows.Count == 0)
                msg="用户名或密码错误！";  //用户名或密码错误

            if (ds.Tables[0].Rows[0]["canlogin"].ToString() != "1")
                msg = "账户已限制登录！";  //不允许登录
                                   //日志
            if (msg != "")
            {
                var log = new BLL.Sys_log();
                var modellog = new Model.Sys_log();
                modellog.EventType = "APP登录";
                modellog.id = Guid.NewGuid().ToString().ToUpper();
                modellog.EventDate = DateTime.Now;
                modellog.UserID = uid;
                modellog.IPStreet = url;
                modellog.Log_Content = ClientId;
                log.Add(modellog);
            }
          

            return Tuple.Create(ds,msg); 
        }

        public static Tuple<DataSet, string> LoginSmall(string WX_ID,string ClientId, string url)
        {

            string sql = "SELECT * FROM  dbo.V_accounts WHERE WX_ID='"+WX_ID+"' ";//AND	 tel='"+ tel + "'
            string msg = "";
            DataSet ds = DBUtility.DbHelperSQL.Query(sql);
            if (ds.Tables[0].Rows.Count == 0)
                msg = "没有此用户,需要注册登录!";  //用户名或密码错误 
                                   //日志
            if (msg != "")
            {
                var log = new BLL.Sys_log();
                var modellog = new Model.Sys_log();
                modellog.EventType = "小程序登录";
                modellog.id = Guid.NewGuid().ToString().ToUpper();
                modellog.EventDate = DateTime.Now;
                modellog.UserID = WX_ID;
                modellog.IPStreet = url;
                modellog.Log_Content = ClientId;
                log.Add(modellog);
            }


            return Tuple.Create(ds, msg);
        }

        public static int updateCusData(string WX_ID, string content, string tel, string type)
        {
            StringBuilder sb = new StringBuilder();

            if (type == "1")
            {
                sb.AppendLine(" UPDATE  dbo.CRM_Customer SET cus_name='" + content + "' WHERE cus_tel='" + tel + "'  AND WX_ID='" + WX_ID + "' ");
            }
            if (type == "2")
            {
                sb.AppendLine(" UPDATE  dbo.CRM_Customer SET cus_add='" + content + "' WHERE cus_tel='" + tel + "' AND WX_ID='" + WX_ID + "' ");
            }
          return DBUtility.DbHelperSQL.ExecuteSql(sb.ToString());
            
        }
            public static Tuple<DataSet, string> SmallReg_Data(string WX_ID,string WX_Name, string tel, string type,string uid)
        {
            StringBuilder sb = new StringBuilder();

            if (type == "cus") {

                sb.AppendLine(" IF EXISTS(SELECT 1 FROM dbo.CRM_Customer WHERE cus_tel='"+tel+"')  ");
                sb.AppendLine(" BEGIN  ");
                sb.AppendLine(" UPDATE  dbo.CRM_Customer SET WX_ID='" + WX_ID + "',cus_name='"+WX_Name+"' WHERE cus_tel='" + tel + "'  ");
                sb.AppendLine(" END	  ");
                sb.AppendLine(" ELSE	  ");
                sb.AppendLine(" BEGIN   ");
                sb.AppendLine(" INSERT INTO dbo.CRM_Customer  ");
                sb.AppendLine(" (  ");
                sb.AppendLine("     id,   ");
                sb.AppendLine("     cus_name,   ");
                sb.AppendLine("     cus_tel,   ");
                sb.AppendLine("     create_id,  ");
                sb.AppendLine("     create_time,  ");
                sb.AppendLine("     WX_ID  ");
                sb.AppendLine(" )  ");
                sb.AppendLine(" VALUES  ");
                sb.AppendLine(" (     ");
                sb.AppendLine(" 	NEWID(),   ");
                sb.AppendLine(" 	'"+ WX_Name + "',   ");
                sb.AppendLine("     '" + tel + "',     ");
                sb.AppendLine(" 	'"+ uid + "',       ");
                sb.AppendLine("     GETDATE(),    ");
                sb.AppendLine("     '"+ WX_ID + "'          ");
                sb.AppendLine("     )   ");
                sb.AppendLine(" END	  ");


            }
            else if (type == "emp")
            {
                sb.AppendLine(" IF EXISTS(SELECT 1 FROM dbo.hr_employee WHERE tel='"+tel+"')  ");
                sb.AppendLine(" BEGIN  ");
                sb.AppendLine(" UPDATE  dbo.hr_employee SET WX_ID='"+WX_ID+"' WHERE tel='" + tel + "'  ");
                sb.AppendLine(" END	  ");
                sb.AppendLine(" ELSE	  ");
                sb.AppendLine(" BEGIN   ");
                sb.AppendLine(" PRINT 111   ");
                //sb.AppendLine(" INSERT INTO dbo.hr_employee  ");
                //sb.AppendLine(" (  ");
                //sb.AppendLine("     id,   ");
                //sb.AppendLine("     name,   ");
                //sb.AppendLine("     tel,   ");
                //sb.AppendLine("     create_id,  ");
                //sb.AppendLine("     create_time,  ");
                //sb.AppendLine("     WX_ID  ");
                //sb.AppendLine(" )  ");
                //sb.AppendLine(" VALUES  ");
                //sb.AppendLine(" (     ");
                //sb.AppendLine(" 	NEWID(),   ");
                //sb.AppendLine(" 	'小程序临时',   ");
                //sb.AppendLine("     '" + tel + "',     ");
                //sb.AppendLine(" 	'" + uid + "',       ");
                //sb.AppendLine("     GETDATE(),    ");
                //sb.AppendLine("     '" + WX_ID + "'          ");
                //sb.AppendLine("     )   ");
                sb.AppendLine(" END	  ");

            }
            DBUtility.DbHelperSQL.ExecuteSql(sb.ToString());
            string sql = "SELECT * FROM  dbo.V_accounts WHERE WX_ID='" + WX_ID + "' ";//AND	 tel='"+ tel + "'
            string msg = "";
            DataSet ds = DBUtility.DbHelperSQL.Query(sql);
            if (ds.Tables[0].Rows.Count == 0)
                msg = "没有此用户,需要注册登录!";  //用户名或密码错误 
            return Tuple.Create(ds, msg);
        }
        public static Tuple<DataSet, string> HomeData(string uid, string userType, string type)
        {

            string errmsg = "";
            string sql = "EXEC Usp_APP_Home '" + uid + "','"+ userType + "','" + type + "'";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<DataSet, string> HomeData2(string category_name, string keyword,string top)
        {
            string imgurl = PubConstant.GetConnectionString("smallAppImgUrl");
            string errmsg = "";
            string strWhere = " WHERE 1=1";
            string sql = " SELECT ";
            if (top != "") sql += " TOP "+top;
              sql += "  id, product_name AS name,agio AS price,'' AS priceMarket,Series_Name+'-'+Level_Name selledNum,'"+ imgurl + "Images/upload/product/" + "'+img  as img  FROM Product  ";
            if (category_name != "") {
                strWhere += " and  category_id='" + category_name+"'";
            }
            if (keyword != "") {
                strWhere += " and (product_name like '%" + keyword + "%' OR Series_Name like '%" + keyword + "%' OR Level_Name like '%" + keyword + "%')";
            }
            DataSet ds = DbHelperSQL.Query(sql+ strWhere);
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }


        public static Tuple<DataSet, string> HomeDataDetail2(string category_name, string keyword)
        {
            string imgurl = PubConstant.GetConnectionString("smallAppImgUrl");
            string errmsg = "";
            string strWhere = " WHERE 1=1";
            string sql = "SELECT *,'" + imgurl + "Images/upload/product/" + "'+img as headimg FROM  dbo.Product  ";
            if (category_name != "")
            {
                strWhere += " and id='" + category_name + "'";
            }
       
            DataSet ds = DbHelperSQL.Query(sql+ strWhere);
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<DataSet, string> HomeData2Para()
        {

            string errmsg = "";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("   ");
            sb.AppendLine("  SELECT * FROM  dbo.Sys_info WHERE sys_key='sys_name'  ");
            sb.AppendLine("  SELECT id, product_category from Product_category   ");

            DataSet ds = DbHelperSQL.Query(sb.ToString());
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<DataSet, string> GetDataNews(string type,string top)
        {
            string imgurl = PubConstant.GetConnectionString("smallAppImgUrl");
            string errmsg = "";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("   ");
            sb.AppendLine("  SELECT top "+top+ " *,'" + imgurl + "Images/upload/news/" + "'+bannerImgUrl as bannerImg FROM  dbo.public_news WHERE 1=1  ");
            if (type != "") {
                sb.AppendLine(" AND news_type='"+type+"'");
            }
            DataSet ds = DbHelperSQL.Query(sb.ToString());
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<DataSet, string> SaleOrderList(string uid, string userType,string PageIndex,string PageSize)
        {

            BLL.Sale_order so = new BLL.Sale_order();
            string strWhere = " 1=1";
            if (userType == "emp")
            {
                strWhere += " AND Sale_order.emp_id='" + uid + "'";
            }
            else if (userType == "cus")
            {
                strWhere += " AND  Sale_order.Customer_id='" + uid + "'";
            }
            string errmsg = "";
            string Total = "";
            DataSet ds = so.GetList(StringPlus.str2int(PageSize), StringPlus.str2int(PageIndex), strWhere, " Sale_order.[Order_date] desc", out Total);
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<DataSet, string> SaleOrderDetail(string id )
        { 
            string errmsg = "";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT * FROM  dbo.Sale_order WHERE  id='"+ id + "'  ");
            sb.AppendLine("   ");
            sb.AppendLine(" SELECT * FROM  dbo.Sale_order_details WHERE order_id='" + id + "'  ");

            DataSet ds = DbHelperSQL.Query(sb.ToString());
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }


        public static Tuple<List<object>, string> smallDataListDetail(string WX_ID,string status,int pageIndex)
        {
            string imgurl = PubConstant.GetConnectionString("smallAppImgUrl");
            string errmsg = ""; 
            string sqlstr = "SELECT * FROM dbo.SmallAPP_Order_Head   WHERE WX_ID='"+ WX_ID + "' AND orderStatus='"+status+"' AND OrderId NOT LIKE 'BuyCar%'";

            string sql = "SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER by CreateTime desc) rowid,* FROM (" +
                        sqlstr + ")AA )Tb WHERE Tb.rowid between " + ((pageIndex-1) * 10) + " AND " + (pageIndex * 10);
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) errmsg = "empty";
            List<object> data = new List<object>();
            foreach (DataRow dr in ds.Tables[0].Rows) {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" SELECT  *,'" + imgurl + "Images/upload/product/" + "'+img as headimg FROM  dbo.smallApp_Order_Body A  ");
                sb.AppendLine(" INNER JOIN dbo.SmallAPP_Order_Head B ON	 B.OrderId = A.OrderId  ");
                sb.AppendLine(" INNER JOIN  dbo.Product C ON A.ProductId=C.id  ");
                sb.AppendLine(" WHERE	 B.WX_ID='" + WX_ID + "' AND	 A.OrderId='" + dr["OrderId"].ToString() + "'  ");
                DataSet ds2 = DbHelperSQL.Query(sb.ToString());
                List<object> item = new List<object>();
                List<object> goods = new List<object>();
                foreach (DataRow dr2 in ds2.Tables[0].Rows)
                {
                    object o = new {
                        goods_id = dr2["ProductId"].ToString(),
                        goods_img = dr2["headimg"].ToString(),
                        goods_name = dr2["product_name"].ToString(),
                        goods_price = dr2["price"].ToString(),
                        goods_buynum = dr2["TotalSum"].ToString()
                    }; 
                    goods.Add(o);
                }
                List<object> ShopItems= new List<object>();//店
                object objitem = new  
                {
                    shopName= "我选购的品种",
                    shopId= "shop100001",
                    goods=goods
                };
                ShopItems.Add(objitem);
                object obj = new
                {
                    status = dr["orderStatusName"].ToString(),
                    statusId = dr["orderStatus"].ToString(),
                    orderNumber = dr["OrderId"].ToString(),
                    orderDate=dr["CreateTime"].ToString(),
                    items= ShopItems
                };
                data.Add(obj);
        }
           
            return Tuple.Create(data, errmsg);
        }

        public static Tuple<DataSet, string> GetBoardDetailData(string barcode)
        {
            string errmsg = "";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT A.*,  ");
            sb.AppendLine(" B.product_name,C.SeriesName,D.LevelName,  ");
            sb.AppendLine(" drom.dormName,cor.corralName, bb.product_category AS catproduct_name,bc.workisstatus,bc.workid ,bc.quantity ,bc.capacity,bc.Order_date,bc.Serialnumber   ");
            sb.AppendLine(" FROM  dbo.Basic_boar A  ");
            sb.AppendLine(" --品类  ");
            sb.AppendLine(" LEFT JOIN dbo.Product B ON	 A.category_id=B.id  ");
            sb.AppendLine("LEFT JOIN dbo.Product_category bb ON bb.id = b.category_id ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_Product_Series C ON	A.Pro_SeriesId=C.id  ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_Product_Series_Level D ON	A.Pro_Series_LevelId=D.id  ");
            sb.AppendLine(" --畜舍  ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_dorm drom ON	 A.dormId=drom.id  ");
            sb.AppendLine(" --畜栏  ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_corral cor ON	A.corralId=cor.id   ");
            sb.AppendLine(" --畜栏状态  ");
            //sb.AppendLine(" LEFT JOIN dbo.Basic_corral_check_log corlog ON	A.dormId=corlog.dornId AND	 A.corralId=corlog.corralId  ");
            sb.AppendLine(" -- 种畜出栏入栏(最近一次top1)  ");
            sb.AppendLine(" --LEFT JOIN (SELECT TOP 1 * FROM  dbo.Basic_Boar_changeLog) blog ON A.id=blog.BoarId  ");
            sb.AppendLine(" --健康  ");
            //sb.AppendLine(" LEFT JOIN  dbo.Basic_Boar_Heath heath ON	A.id=heath.boarId  ");
            sb.AppendLine(" --用药(也需要筛选最近一次)  ");
            //sb.AppendLine(" --LEFT JOIN  dbo.Basic_Boar_Drug drug ON	 A.id=drug.boarId	  ");
            sb.AppendLine("LEFT JOIN(SELECT a.boarid, a.IsStatus AS workisstatus, a.id AS workid, b.quantity, b.capacity, c.Order_date,c.Serialnumber FROM boar_collection a ");
            sb.AppendLine("LEFT JOIN Sale_order_details b ON b.order_id= a.orderid AND b.product_id= a.ordermxid ");
            sb.AppendLine(" LEFT JOIN dbo.Sale_order c ON c.id= b.order_id WHERE a.IsStatus= '10') bc ON bc.boarid = a.id ");
            sb.AppendLine("   where A.barcode='"+ barcode + "' ");


            DataSet ds = DbHelperSQL.Query(sb.ToString());
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static Tuple<int, string> UpdateCollectionOrder(string CId,string uid)
        {

            string sql = "UPDATE boar_collection SET	IsStatus='20',collectDate=GETDATE(),collector='"+uid+"' WHERE id='" + CId + "' ";//AND	 tel='"+ tel + "'
            sql += " UPDATE B SET LastCollecTime = GETDATE()";
            sql += " FROM dbo.boar_collection A";
            sql += " INNER JOIN Basic_boar B ON a.boarid = B.id WHERE A.id = '" + CId + "'";
            string msg = "";
           int i = DBUtility.DbHelperSQL.ExecuteSql(sql);
            if (i<= 0)
                msg = "修改失败!";
            BLL.Sys_log log = new Sys_log();
            Model.Sys_log logM = new Model.Sys_log();
            logM.EventID = CId;
            logM.UserID = uid;
            logM.EventTitle = CId + "更新状态";
            logM.EventDate = DateTime.Now;
            logM.Log_Content = CId + "状态改为20";
            logM.id = Guid.NewGuid().ToString().ToUpper(); 
            logM.IPStreet = "APP";

            logM.EventType ="工单操作"; 
            log.Add(logM);
            return Tuple.Create(i, msg);
        }

        public static Tuple<DataSet, string> ColloctionOrderList(string status, string bid,string startdate, string enddate)
        {

            BLL.boar_collection so = new BLL.boar_collection();
            string strWhere = " 1=1"; 
            if(status!="")
            strWhere += " AND a.IsStatus in('"+status+"')";
            if(startdate!="")
            strWhere += " AND collectDate >='"+startdate+"'";
            if (enddate != "")
                strWhere += " AND collectDate <='" + enddate + "'";
            if (bid != "")
                strWhere += " AND A.boarid  ='" + bid + "'";

            string errmsg = "";
            DataSet ds = so.GetList(strWhere);
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);
        }

        public static string ChangeCheck(string dormId, string CorralId)
        {
            string sql = (" SELECT * FROM dbo.Basic_boar WHERE dormId='" + dormId + "' AND corralId='" + CorralId + "'  ");
            DataSet ds = DBUtility.DbHelperSQL.Query(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return  "调入舍栏已被" + ds.Tables[0].Rows[0]["barcode"].ToString() + "使用!" ;
            }
            else return "";
        }

        public static string saveChange(string type,string BoarId,string DormId,string CorralId,string emp_name,string Nobid)
        {
          BLL.Basic_Boar_changeLog category = new BLL.Basic_Boar_changeLog();
         Model.Basic_Boar_changeLog model = new Model.Basic_Boar_changeLog();
              model.remarks = type;
            model.BoarId = BoarId;
            if (type == "In")
            {
                model.InTime = DateTime.Now;
                model.InDormId = DormId;
                model.InCorralId = CorralId; string sqlstr = "       SELECT * FROM dbo.Basic_boar WHERE dormId = '" + model.InDormId + "' AND corralId = '" + model.InCorralId + "'  ";
                DataSet ds = DBUtility.DbHelperSQL.Query(sqlstr);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return  ds.Tables[0].Rows[0]["barcode"].ToString() + "已经占用此位置!" ;
                }
                else
                {
                    string sql = "UPDATE dbo.Basic_boar SET dormId='" + model.InDormId + "' ,corralId='" + model.InCorralId + "' WHERE id='" + model.BoarId + "'";
                    DBUtility.DbHelperSQL.ExecuteSql(sql);
                    model.id = Guid.NewGuid().ToString();
                    model.accpetPerson = emp_name;
                    category.Add(model);
                }
            }
            if (type == "Out")
            {
                string sql = "UPDATE dbo.Basic_boar SET dormId='' ,corralId='' WHERE id='" + model.BoarId + "' ;";
                sql += " UPDATE dbo.Basic_Boar_changeLog SET OutTime=GETDATE(),OutDormId='" + emp_name + "' WHERE BoarId='" + model.BoarId + "' AND OutTime IS NULL";
                DBUtility.DbHelperSQL.ExecuteSql(sql);
            }
            if (type == "Change")
            {
               
                if (Nobid == "Y")//直接入舍{}
                {
                    model.InTime = DateTime.Now;
                    model.InDormId = DormId;
                    model.InCorralId = CorralId;
                    string sql = "UPDATE dbo.Basic_boar SET dormId='" + model.InDormId + "' ,corralId='" + model.InCorralId + "' WHERE id='" + model.BoarId + "'";
                    DBUtility.DbHelperSQL.ExecuteSql(sql);
                    model.id = Guid.NewGuid().ToString();
                    model.accpetPerson = emp_name;
                    category.Add(model);
                }
                else if (Nobid == "N")
                { //调换

                    //   DataSet ds = category.GetList(" dormId='" + PageValidate.InputText(request["DormId"], 50) + "' and corralId='" + PageValidate.InputText(request["CorralId"], 50) + "'");
                  
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(" --先取出选择的舍/栏 的种禽  ");
                    sb.AppendLine(" DECLARE @Old_Id VARCHAR(50)  ");
                    sb.AppendLine(" SELECT @Old_Id=id FROM dbo.Basic_boar WHERE dormId='" + DormId + "' AND corralId='" + CorralId + "'  ");
                    sb.AppendLine("   ");
                    sb.AppendLine(" DECLARE @dormId VARCHAR(50)  ");
                    sb.AppendLine(" DECLARE @corralId	 VARCHAR(50)  ");
                    sb.AppendLine(" --再取出当前种禽的 舍/栏  ");
                    sb.AppendLine(" SELECT @dormId=dormId,@corralId=corralId FROM Basic_boar WHERE id='" + BoarId + "'  ");
                    sb.AppendLine("   ");
                    sb.AppendLine(" IF @dormId='" + DormId + "' AND @corralId='" + CorralId + "'  ");
                    sb.AppendLine(" BEGIN  ");
                    sb.AppendLine(" RAISERROR('选择的舍/栏不能完全相同!',18,6)   ");
                    sb.AppendLine(" RETURN;  ");
                    sb.AppendLine(" END  ");
                    sb.AppendLine(" --释放 需要的 舍/栏  把当前的舍/栏更新给需要的(调换)  ");
                    sb.AppendLine(" UPDATE Basic_boar SET dormId=@dormId,corralId=@corralId WHERE id=@Old_Id  ");
                    sb.AppendLine(" --出栏  ");
                    sb.AppendLine(" UPDATE Basic_Boar_changeLog SET	 OutTime=GETDATE() WHERE id=@Old_Id  ");
                    sb.AppendLine(" --调换  ");
                    sb.AppendLine(" UPDATE Basic_boar SET	dormId='" + DormId + "' , corralId='" + CorralId + "' WHERE id='" + BoarId + "'  ");
                    sb.AppendLine(" --入舍  ");
                    sb.AppendLine(" INSERT INTO dbo.Basic_Boar_changeLog  ");
                    sb.AppendLine(" (  ");
                    sb.AppendLine("     id,  ");
                    sb.AppendLine("     BoarId,  ");
                    sb.AppendLine("     InDormId,  ");
                    sb.AppendLine("     InCorralId,  ");
                    sb.AppendLine("     InTime,   ");
                    sb.AppendLine("     accpetPerson,  ");
                    sb.AppendLine("     remarks  ");
                    sb.AppendLine(" )  ");
                    sb.AppendLine(" VALUES  ");
                    sb.AppendLine(" (   NEWID(),        -- id - varchar(50)  ");
                    sb.AppendLine("     '" + BoarId + "',        -- BoarId - varchar(50)  ");
                    sb.AppendLine("     '" + DormId + "',        -- InBormId - varchar(50)  ");
                    sb.AppendLine("     '" + CorralId + "',        -- InCorralId - varchar(50)  ");
                    sb.AppendLine("     GETDATE(), -- InTime - datetime   ");
                    sb.AppendLine("     '" + emp_name + "',        -- accpetPerson - varchar(50)  ");
                    sb.AppendLine("     '入舍'         -- remarks - varchar(50)  ");
                    sb.AppendLine("     )  ");
                    sb.AppendLine("   ");
                    sb.AppendLine("   ");
                    try
                    {
                        DBUtility.DbHelperSQL.ExecuteSql(sb.ToString());
                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                }



            }

            return "";
        }


        public static Tuple<DataSet, string> changeLog(string bid,string dormId,string corralId,string startdate,string enddate)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT A.*,B.dormName,c.corralName,D.barcode   ");
            sb.AppendLine(" FROM dbo.Basic_Boar_changeLog A  ");
            sb.AppendLine(" INNER JOIN  dbo.Basic_dorm B ON A.InDormId=B.id  ");
            sb.AppendLine(" INNER JOIN  dbo.Basic_corral c ON A.InCorralId=c.id  ");
            sb.AppendLine(" INNER JOIN  dbo.Basic_boar D ON	A.BoarId =D.id  ");
            sb.AppendLine(" where 1=1");
            if (bid != "")
                sb.AppendLine(" and a.BoarId='"+bid+"'");
            if (dormId != "")
                sb.AppendLine(" and a.InDormId='" + dormId + "'");
            if (corralId != "")
                sb.AppendLine(" and a.InCorralId='" + corralId + "'");
            if (startdate != "")
                sb.AppendLine(" and a.InTime>='" + startdate + "'");
            if (enddate != "")
                sb.AppendLine(" and a.InTime<='" + enddate + "'");
            string errmsg = "";
            DataSet ds = DbHelperSQL.Query(sb.ToString());
            if (ds == null) errmsg = "查询错误";
            return Tuple.Create(ds, errmsg);

        }


    }

}